import time
import os,sys
sys.path.append(os.path.dirname(sys.path[0]))
import shapely
from shapely.geometry import MultiPolygon,Polygon
import json
from fastapi import Depends
from db.db_operate import DB
from curd.Filter2SQL import Filter2SqlWhere as cqlfilter
from curd.sql2stac import Sqlresult2Stac
from fastapi import APIRouter
from schemas.stacsearchbody import BaseSearchPostRequest
postserch = APIRouter()



@postserch.post("/search")
def stacsearch(serach:BaseSearchPostRequest,session=Depends(DB),sql=Depends(Sqlresult2Stac)):
   
    starttime=time.time()
    limit=serach.limit
    filter=serach.filter
    page=serach.page
    joinjudge=False
    if filter=={} or not filter:
        if serach.collections or serach.ids or serach.bbox or serach.intersects :
            where="where"
            if serach.collections:
                if len(serach.collections) > 0:
                    if len(serach.collections) == 1:
                        serach.collections.append("XXXXXXX")
                    inlist = tuple(i for i in serach.collections)
                    if len(where)>5:
                        where += " and collections in " + str(inlist)
                    else:
                        where += " collections in " + str(inlist)
            if serach.ids:
                if len(serach.ids) > 0:
                    if len(serach.ids) == 1:
                        serach.ids.append("XXXXXXX")
                    inlist = tuple(i for i in serach.ids)
                    if len(where)>5:
                        where += " and id in " + str(inlist)
                    else:
                        where += " id in " + str(inlist)
            if serach.bbox:
                if len(serach.bbox) == 4:
                    geowkt = shapely.to_wkt(
                        shapely.box(float(serach.bbox[0]), float(serach.bbox[1]), float(serach.bbox[2]),
                                    float(serach.bbox[3])))
                    if len(where)>5:
                        where += " and ST_WITHIN({},({}))".format("geometry", "'" + geowkt + "'")
                    else:
                        where += " ST_WITHIN({},({}))".format("geometry",
                                                                                                    "'" + geowkt + "'")
            if serach.intersects:
                geowkt = serach.intersects.wkt

                geo = shapely.from_wkt(geowkt)
                if type(geo) == MultiPolygon:
                    geos = shapely.unary_union(geo).convex_hull
                    geowkt = shapely.to_wkt(geos)
                    #bounds = geo.bounds
                    #geowkt = shapely.to_wkt(shapely.box(bounds[0], bounds[1], bounds[2], bounds[3]))
                    # combined_polygon = geo.convex_hull
                    #
                    # geowkt = shapely.to_wkt(combined_polygon)
                elif type(geo) == Polygon:
                    polygoncount = len(geo.exterior.xy[0])
                    if polygoncount > 50:
                        geos = shapely.unary_union(geo).convex_hull
                        geowkt = shapely.to_wkt(geos)
                #geowkt = shapely.to_wkt(shapely.from_geojson(json.dumps(serach.intersects)))
                if len(where) > 5:
                    where += " and ST_INTERSECTS({},({}))".format("geometry", "'" + geowkt + "'")
                else:
                    where += " ST_INTERSECTS({},({}))".format("geometry",
                                                                                                    "'" + geowkt + "'")
        else:
            where=''
        wherecount=where
        if serach.sortby:
            where += " order by "
            sortbylist = []
            for i in range(len(serach.sortby)):
                if serach.sortby[i]["field"]=="price" or serach.sortby[i]["field"]=="score":
                    joinjudge =True
                sortbylist.append(" " + serach.sortby[i]["field"] + " " + serach.sortby[i]["direction"])
            where += ",".join(sortbylist)

        if joinjudge:
            sqltext ="select count(*) from ki_stac.daily_data_test e join ki_home.daily_data_view m on e.SlID=m.SlID and e.ImgGSD=m.ImgGSD "+wherecount
            sqlall="select content from ki_stac.daily_data_test e join ki_home.daily_data_view m on e.SlID=m.SlID and e.ImgGSD=m.ImgGSD "+where
        else:
            sqltext ="select count(*) from ki_stac.daily_data_test "+wherecount
            sqlall="select content from ki_stac.daily_data_test "+where
       
        countdict = session.execute(sqltext, limit, 0)
    else:
        cqltrans_list=cqlfilter(filter).query_to_sql()
       
        where=cqltrans_list[0]
        joinfilterjudge=cqltrans_list[1]
        if serach.collections:
            if len(serach.collections)>0:
                if len(serach.collections)==1:
                    serach.collections.append("XXXXXXX")
                inlist = tuple(i for i in serach.collections)
                where+=" and collections in "+str(inlist)
        if serach.ids:
            if len(serach.ids)>0:
                if len(serach.ids)==1:
                    serach.ids.append("XXXXXXX")
                inlist = tuple(i for i in serach.ids)
                where += " and id in " + str(inlist)
        if serach.bbox:
            if len(serach.bbox)==4:
                geowkt = shapely.to_wkt(shapely.box(float(serach.bbox[0]),float(serach.bbox[1]),float(serach.bbox[2]),float(serach.bbox[3])))
                where +=" and ST_WITHIN({},ST_SIMPLIFYPRESERVETOPOLOGY(({}),0.01))".format("geometry", "'" + geowkt + "'")
        if serach.intersects:
            geowkt=serach.intersects.wkt
            geo = shapely.from_wkt(geowkt)
            if type(geo) == MultiPolygon:
                geos=shapely.unary_union(geo).convex_hull
                geowkt=shapely.to_wkt(geos)
                #bounds = geo.bounds
                #geowkt=shapely.to_wkt(shapely.box(bounds[0],bounds[1],bounds[2],bounds[3]))
                # combined_polygon=geo.convex_hull  多面化单面
                # geowkt=shapely.to_wkt(combined_polygon)
            elif type(geo) == Polygon:
                polygoncount=len(geo.exterior.xy[0])
                if polygoncount>50:
                    geos = shapely.unary_union(geo).convex_hull
                    geowkt = shapely.to_wkt(geos)
            #geowkt = shapely.to_wkt(shapely.from_geojson(json.dumps(serach.intersects)))
            where +=" and ST_INTERSECTS({},({}))".format("geometry", "'" + geowkt + "'")
        wherecount=where
        if serach.sortby:
            where+=" order by "
            sortbylist=[]
            for i in range(len(serach.sortby)):
                sortbylist.append(" "+serach.sortby[i]["field"]+" "+serach.sortby[i]["direction"])
                if serach.sortby[i]["field"]=="price" or serach.sortby[i]["field"]=="score":
                    joinjudge =True
            where+=",".join(sortbylist)
 
        if joinjudge or joinfilterjudge:
            sqltext="select count(*) from ki_home.daily_data_view  e join ki_stac.daily_data_test m on e.SlID=m.SlID and e.ImgGSD=m.ImgGSD where {}".format(wherecount)    
            sqlall="select content from ki_home.daily_data_view  e join ki_stac.daily_data_test m on e.SlID=m.SlID and e.ImgGSD=m.ImgGSD where {}".format(where)
        else:
            sqltext="select count(*) from ki_stac.daily_data_test where {}".format(wherecount)    
            sqlall="select content from ki_stac.daily_data_test where {}".format(where)
        
        countdict=session.execute(sqltext,limit,0)
    allcount=sql.values(countdict,1)[0]
    features=[]
    context={}
    print(sqlall)
    if allcount>0:
        offset=(page-1)*limit
        resultdict=session.execute(sqlall,limit,offset)
        # features=sql.merfeild(resultdict)
        propertieslist=sql.values(resultdict,1)
        for feature in propertieslist:
            features.append(json.loads(feature))
    context["total"] = allcount
    context["limit"]=limit
    context["return"]=len(features)
    context["page"]=page
    context["totalpage"]=(allcount-1)//limit+1
    result=sql.to_stacdict(context,features)
    result["context"]["time_consume"]= time.time()-starttime
    return result

@postserch.post("/result_extent")
def result_extent(serach:BaseSearchPostRequest,session=Depends(DB),sql=Depends(Sqlresult2Stac)):
    filter = serach.filter
    page = serach.page
    joinjudge=False
    if filter == {} or not filter:
        if serach.collections or serach.ids or serach.bbox or serach.intersects:
            where = "where"
            if serach.collections:
                if len(serach.collections) > 0:
                    if len(serach.collections) == 1:
                        serach.collections.append("XXXXXXX")
                    inlist = tuple(i for i in serach.collections)
                    if len(where) > 5:
                        where += " and collections in " + str(inlist)
                    else:
                        where += " collections in " + str(inlist)
            if serach.ids:
                if len(serach.ids) > 0:
                    if len(serach.ids) == 1:
                        serach.ids.append("XXXXXXX")
                    inlist = tuple(i for i in serach.ids)
                    if len(where) > 5:
                        where += " and id in " + str(inlist)
                    else:
                        where += " id in " + str(inlist)
            if serach.bbox:
                if len(serach.bbox) == 4:
                    geowkt = shapely.to_wkt(
                        shapely.box(float(serach.bbox[0]), float(serach.bbox[1]), float(serach.bbox[2]),
                                    float(serach.bbox[3])))
                    if len(where) > 5:
                        where += " and ST_WITHIN({},ST_SIMPLIFYPRESERVETOPOLOGY(({}),0.01))".format("geometry",
                                                                                                    "'" + geowkt + "'")
                    else:
                        where += " ST_WITHIN({},ST_SIMPLIFYPRESERVETOPOLOGY(({}),0.01))".format("geometry",
                                                                                                "'" + geowkt + "'")
            if serach.intersects:
                geowkt = serach.intersects.wkt

                geo = shapely.from_wkt(geowkt)
                if type(geo) == MultiPolygon:
                    geos = shapely.unary_union(geo).convex_hull
                    geowkt = shapely.to_wkt(geos)
                    # bounds = geo.bounds
                    # geowkt = shapely.to_wkt(shapely.box(bounds[0], bounds[1], bounds[2], bounds[3]))
                    # combined_polygon = geo.convex_hull
                    #
                    # geowkt = shapely.to_wkt(combined_polygon)
                elif type(geo) == Polygon:
                    polygoncount = len(geo.exterior.xy[0])
                    if polygoncount > 50:
                        geos = shapely.unary_union(geo).convex_hull
                        geowkt = shapely.to_wkt(geos)
                # geowkt = shapely.to_wkt(shapely.from_geojson(json.dumps(serach.intersects)))
                if len(where) > 5:
                    where += " and ST_INTERSECTS({},ST_SIMPLIFYPRESERVETOPOLOGY(({}),0.01))".format("geometry",
                                                                                                    "'" + geowkt + "'")
                else:
                    where += " ST_INTERSECTS({},ST_SIMPLIFYPRESERVETOPOLOGY(({}),0.01))".format("geometry",
                                                                                                "'" + geowkt + "'")

        else:
            where = ''
        wherecount=where
        if serach.sortby:
            for i in range(len(serach.sortby)):
                if serach.sortby[i]["field"]=="price" or serach.sortby[i]["field"]=="score":
                    joinjudge =True
        if joinjudge:
            sqltext = "select count(*) from ki_stac.daily_data_test e join ki_home.daily_data_view m on e.SlID=m.SlID and e.ImgGSD=m.ImgGSD " + wherecount
            sqlall = "select content from ki_stac.daily_data_test e join ki_home.daily_data_view m on e.SlID=m.SlID and e.ImgGSD=m.ImgGSD " + where
            sqlextent ="select ST_DISSOLVEOVERLAPPING(geometry) as ge from ki_stac.daily_data_test e join ki_home.daily_data_view m on e.SlID=m.SlID and e.ImgGSD=m.ImgGSD " + where
        else:
            sqltext = "select count(*) from ki_stac.daily_data_test " + wherecount
            sqlall = "select content from ki_stac.daily_data_test " + where
            sqlextent ="select ST_DISSOLVEOVERLAPPING(geometry) as ge from ki_stac.daily_data_test " + where
        countdict = session.execute(sqltext, 1, 0)
        #if serach.sortby:
            #where += " order by " + serach.sortby[0].field + " " + serach.sortby[0].direction
        
    else:
        cqltrans_list=cqlfilter(filter).query_to_sql()
        where=cqltrans_list[0]
        joinfilterjudge=cqltrans_list[1]
        if serach.collections:
            if len(serach.collections) > 0:
                if len(serach.collections) == 1:
                    serach.collections.append("XXXXXXX")
                inlist = tuple(i for i in serach.collections)
                where += " and collections in " + str(inlist)
        if serach.ids:

            if len(serach.ids) > 0:
                if len(serach.ids) == 1:
                    serach.ids.append("XXXXXXX")
                inlist = tuple(i for i in serach.ids)
                where += " and id in " + str(inlist)
        if serach.bbox:

            if len(serach.bbox) == 4:
                geowkt = shapely.to_wkt(shapely.box(float(serach.bbox[0]), float(serach.bbox[1]), float(serach.bbox[2]),
                                                    float(serach.bbox[3])))
                where += " and ST_WITHIN({},ST_SIMPLIFYPRESERVETOPOLOGY(({}),0.01))".format("geometry",
                                                                                            "'" + geowkt + "'")
        if serach.intersects:

            geowkt = serach.intersects.wkt
            geo = shapely.from_wkt(geowkt)

            if type(geo) == MultiPolygon:
                geos = shapely.unary_union(geo).convex_hull
                geowkt = shapely.to_wkt(geos)
                # bounds = geo.bounds
                # geowkt=shapely.to_wkt(shapely.box(bounds[0],bounds[1],bounds[2],bounds[3]))
                # combined_polygon=geo.convex_hull  多面化单面
                # geowkt=shapely.to_wkt(combined_polygon)
            elif type(geo) == Polygon:
                polygoncount = len(geo.exterior.xy[0])
                if polygoncount > 50:
                    geos = shapely.unary_union(geo).convex_hull
                    geowkt = shapely.to_wkt(geos)

            # geowkt = shapely.to_wkt(shapely.from_geojson(json.dumps(serach.intersects)))
            where += " and ST_INTERSECTS({},ST_SIMPLIFYPRESERVETOPOLOGY(({}),0.01))".format("geometry","'" + geowkt + "'")
        wherecount=where        
        if serach.sortby:
            for i in range(len(serach.sortby)):
                if serach.sortby[i]["field"]=="price" or serach.sortby[i]["field"]=="score":
                    joinjudge =True
        if joinjudge or joinfilterjudge:  
            sqltext = "select count(*) from ki_stac.daily_data_test e join ki_home.daily_data_view m on e.SlID=m.SlID and e.ImgGSD=m.ImgGSD where {}".format(wherecount)
            #if serach.sortby:
                #where += " order by " + serach.sortby[0].field + " " + serach.sortby[0].direction
            sqlall = "select content from ki_stac.daily_data_test e join ki_home.daily_data_view m on e.SlID=m.SlID and e.ImgGSD=m.ImgGSD where {}".format(where)
            sqlextent="select ST_DISSOLVEOVERLAPPING(geometry) as ge from ki_stac.daily_data_test e join ki_home.daily_data_view m on e.SlID=m.SlID and e.ImgGSD=m.ImgGSD where {}".format(where)

        else:

            sqltext = "select count(*) from ki_stac.daily_data_test where {}".format(wherecount)
            #if serach.sortby:
                #where += " order by " + serach.sortby[0].field + " " + serach.sortby[0].direction
            sqlall = "select content from ki_stac.daily_data_test where {}".format(where)
            sqlextent="select ST_DISSOLVEOVERLAPPING(geometry) as ge from ki_stac.daily_data_test where {}".format(where)
        countdict = session.execute(sqltext, 1, 0)
    allcount = sql.values(countdict, 1)[0]
    
    if allcount > 0 and allcount<100000:

        resultdict = session.execute(sqlextent, 1, 0)
        resultwktlist = sql.values(resultdict, 1)
        resultgeojson=json.loads(shapely.to_geojson(shapely.from_wkt(resultwktlist[0])))
        return resultgeojson
    else:
        return {}

if __name__ == "__main__":
    import uvicorn
    #uvicorn.run(app, host="0.0.0.0", port=8086,debug=True)
    uvicorn.run("main:app", host="0.0.0.0", port=18082,reload=True)
